package by.epam.communalservices.utils;

import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import by.epam.communalservices.entity.JkhTeam;
import by.epam.communalservices.entity.JkhWorker;
import by.epam.communalservices.exception.JkhDaoException;

public class Staff {

    private final String COLUMN_TEAM_ID = "jkh_team_id";

    private final String COLUMN_OWNER_ID = "brig_owner_id";

    private final String COLUMN_SURNAME = "brig_surname";

    private final String COLUMN_NAME = "brig_name";

    private final String COLUMN_PATRONYMIC = "brig_patronymic";


    private final String ID_WORKER = "jkh_worker_id";

    private Connection con;

    static Logger JdbcDaoStaffLoger = Logger.getLogger(Staff.class.getName());

    public Staff(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException("Connection error. Connection is not open", new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error", new Exception());

        }
    }

    public List<Long> getStaff(Long teamId) {
        @SuppressWarnings("unchecked")
        List<Long> list = Collections.EMPTY_LIST;
        JdbcDaoStaffLoger.debug("List<Long> getStaff(Long teamId)");
        JdbcDaoStaffLoger.info("method returns a list of id_worker");
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT public.jkh_team_staff.jkh_worker_id " + 
                             " from public.jkh_team_staff "+ 
                             " where  public.jkh_team_staff.jkh_team_id = ?";
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, teamId);
                result = pstate.executeQuery();
                list = new ArrayList<Long>();
                while (result.next()) {
                    list.add(result.getLong(ID_WORKER));
                }

            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("Error compiling the list", ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }
        return list;
    }

    public void setStaff(Long teamId, List<Long> list) {
        JdbcDaoStaffLoger.debug("setStaff(Long teamId, List<Long> list)");
        JdbcDaoStaffLoger.info("method set list of id_worker");
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                deleteStaff(teamId);
                String sql = "INSERT INTO public.jkh_team_staff " + "(jkh_team_id,jkh_worker_id) VALUES (?,?)";
                pstate = con.prepareStatement(sql);
                for (Long id : list) {
                    pstate.setLong(1, teamId);
                    pstate.setLong(2, id);
                    result = pstate.executeQuery();
                }

            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("Error adding a record", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
    }

    public void deleteStaff(Long teamId) {
        JdbcDaoStaffLoger.debug("deleteStaff(Long teamId)");
        JdbcDaoStaffLoger.info("method delete id_worker");
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                String sql = "DELETE FROM public.jkh_team_staff WHERE " + "public.jkh_team_staff.jkh_team_id = ?";
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, teamId);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("Failure to remove the id_worker", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
    }

    public Long countStaff(Long teamId) {
        JdbcDaoStaffLoger.debug("Long countStaff(Long teamId)");
        JdbcDaoStaffLoger.info("method return count worker");
        PreparedStatement pstate = null;
        ResultSet result = null;
        Long countRecord = null;
        try {
            try {
                String sql = "SELECT COUNT(*) AS count FROM public.jkh_team_staff " + 
                             "WHERE jkh_team_id = ?";
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, teamId);
                result = pstate.executeQuery();
                if (result.next()) {
                    countRecord = result.getLong("count");
                } else {
                    throw new SQLException();
                }

            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("Failed to get count worker records", ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }
        return countRecord;
    }

    
    public Long countState(Long teamId, String state) {
        JdbcDaoStaffLoger.debug("Long countState(Long "+teamId+", String "+state+")");
        JdbcDaoStaffLoger.info("method return count worker");
        PreparedStatement pstate = null;
        ResultSet result = null;
        Long countRecord = null;
        try {
            try {
                String sql = "SELECT COUNT(*) AS count FROM public.jkh_task " + 
                             "WHERE team_id = ? AND state = ?  ";
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, teamId);
                pstate.setString(2, state);
                result = pstate.executeQuery();
                if (result.next()) {
                    countRecord = result.getLong("count");
                } else {
                    throw new SQLException();
                }

            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("Failed to get count worker records", ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }
        return countRecord;
    }
    
    public List<JkhWorker> getFreeWorker() {
        JdbcDaoStaffLoger.debug("JkhWorker getFreeWorker(Long teamId)");
        JdbcDaoStaffLoger.info("method read team");
        @SuppressWarnings("unchecked")
        List<JkhWorker> list = Collections.EMPTY_LIST;
        ResultSet result = null;
        PreparedStatement pstate = null;
        
        String sql = "SELECT * FROM public.jkh_worker " +
                "WHERE jkh_worker_id " + 
                "NOT IN(SELECT jkh_worker_id " +
                "FROM public.jkh_team_staff) ";
        try {
            try {
                pstate = con.prepareStatement(sql);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("query execute error", ex);
            }
            try {
                    list = new ArrayList<JkhWorker>();
                    while (result.next()) {
                        JkhWorker jkhWorker = new JkhWorker(
                                result.getLong("jkh_worker_id"), 
                                result.getString("surname"), 
                                result.getString("name"), 
                                result.getString("patronymic"), 
                                result.getString("specialty"), 
                                result.getInt("rate"));
                        list.add(jkhWorker);
                    }
            } catch (SQLException ex) {
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }
        return list;
    }
    
    public List<JkhTeam> getWorkerStaff(Long workerId) {

        JdbcDaoStaffLoger.debug("JkhTeam getWorkerStaff(Long workerId)");
        JdbcDaoStaffLoger.info("method read team");
        @SuppressWarnings("unchecked")
        List<JkhTeam> list = Collections.EMPTY_LIST;
        ResultSet result = null;
        PreparedStatement pstate = null;
        String sql = "SELECT * FROM public.jkh_team where " +
        		"public.jkh_team.jkh_team_id in " + 
        		"(SELECT public.jkh_team_staff.jkh_team_id " +
        		"FROM public.jkh_team_staff " + 
        		"WHERE jkh_worker_id = ?) ";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, workerId);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("query execute error", ex);
            }
            try {
                    list = new ArrayList<JkhTeam>();
                    while (result.next()) {
                    JkhTeam jkhTeam = new JkhTeam(
                                result.getLong(COLUMN_TEAM_ID), 
                                result.getLong(COLUMN_OWNER_ID),
                                result.getString(COLUMN_SURNAME), 
                                result.getString(COLUMN_NAME),
                                result.getString(COLUMN_PATRONYMIC));
                        list.add(jkhTeam);
                    }

            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("Record not found", ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }
        return list;
    }

    public void addWorkerStaff(Long workerId, Long teamId) {
        JdbcDaoStaffLoger.debug("addWorkerStaff(Long workerId, Long teamId)");
        JdbcDaoStaffLoger.info("method add worker in team");
        ResultSet result = null;
        PreparedStatement pstate = null;
        String sql = "INSERT INTO public.jkh_team_staff " + 
                     "(jkh_team_id, jkh_worker_id) VALUES (?,?)";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, teamId);
                pstate.setLong(2, workerId);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("query execute error", ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }
    }

    public void removeWorkerStaff(Long workerId, Long teamId) {
        JdbcDaoStaffLoger.debug("removeWorkerStaff(Long workerId, Long teamId)");
        JdbcDaoStaffLoger.info("method remove worker from team");
        ResultSet result = null;
        PreparedStatement pstate = null;
        String sql = "DELETE FROM public.jkh_team_staff " + 
                     "WHERE jkh_worker_id = ?  and jkh_team_id = ?";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, workerId);
                pstate.setLong(2, teamId);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoStaffLoger.error("query execute error", ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close result", ex);
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoStaffLoger.error("Error close statement", ex);
                }
        }

    }
}
